﻿using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Shuba.Model;
using System;

namespace Shuba.Data
{
	public class ClusterTarget
	{
        public ClusterTarget()
		{
		}

		public List<ClusterTargetInfo> GetList(string RegionalID, string TargetYear, string CostCenter)
		{
            List<ClusterTargetInfo> list = new List<ClusterTargetInfo>();
            //Database db = DatabaseFactory.CreateDatabase();
			Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            		                    A.CLUSTER_ID, 
            		                    A.CLUSTER_NAME,
            		                    ISNULL(B.TARGET_YEAR,'') AS TARGET_YEAR,
                                        ISNULL(B.TARGET_AMOUNT,0) AS TARGET_AMOUNT,
            		                    ISNULL(B.NOA,0) AS NOA,
                                        ISNULL(B.TARGET_AMOUNT_REAL,0) AS TARGET_AMOUNT_REAL,
            		                    ISNULL(B.NOA_REAL,0) AS NOA_REAL,
            		                    ISNULL(B.ENTRY_BY,'') AS ENTRY_BY,
            		                    ISNULL(B.ENTRY_DATE,getdate()) AS ENTRY_DATE,
            		                    ISNULL(B.UPDATE_BY, '') AS UPDATE_BY,
            		                    ISNULL(B.UPDATE_DATE, getdate()) AS UPDATE_DATE
                                FROM TBL_MS_CLUSTER A INNER JOIN 
                                (
                                    SELECT B.CLUSTER_ID
                                    FROM
                                        TBL_MS_CLUSTER_BRANCH A INNER JOIN 
                                        TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID LEFT JOIN
                                        TBL_MS_HO_REGION C ON B.REGIONAL_ID = C.REGIONAL_ID
                                    WHERE 
                                        ((C.HO_ID = @COST_CENTER) OR (B.REGIONAL_ID = @COST_CENTER) OR (A.CLUSTER_ID = @COST_CENTER) OR (A.BRANCH_ID = @COST_CENTER))
                                        GROUP BY B.CLUSTER_ID
                                )C ON A.CLUSTER_ID = C.CLUSTER_ID LEFT OUTER JOIN 
                                (
            	                    SELECT * FROM TBL_CLUSTER_TARGET_PLAN 
            	                    WHERE TARGET_YEAR = '' + @TARGET_YEAR + ''
                                ) B ON C.CLUSTER_ID = B.CLUSTER_ID 
                                ");
            db.AddInParameter(cmd, "@REGIONAL_ID", DbType.String, RegionalID);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            db.AddInParameter(cmd, "@COST_CENTER", DbType.String, CostCenter);
			using (IDataReader dr = db.ExecuteReader(cmd))
			{
				while (dr.Read())
				{
                    list.Add(new ClusterTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10)));
				}
                dr.Close();
			}
			return list;
		}

        public List<ClusterTargetInfo> GetListApproval(string TargetYear, string UpdateBy, string CostCenter)
        {
            List<ClusterTargetInfo> list = new List<ClusterTargetInfo>();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            		                    A.CLUSTER_ID, 
            		                    A.CLUSTER_NAME,
            		                    B.TARGET_YEAR,
                                        B.TARGET_AMOUNT,
            		                    B.NOA,
                                        B.TARGET_AMOUNT_REAL,
            		                    B.NOA_REAL,
            		                    B.ENTRY_BY,
            		                    B.ENTRY_DATE,
            		                    B.UPDATE_BY,
            		                    B.UPDATE_DATE,
                                        B.ACT_TYPE
                                FROM TBL_MS_CLUSTER A INNER JOIN TBL_CLUSTER_TARGET_PLAN_TMP B
                                ON A.CLUSTER_ID = B.CLUSTER_ID INNER JOIN 
                                (
                                    SELECT B.CLUSTER_ID
                                    FROM
                                        TBL_MS_CLUSTER_BRANCH A INNER JOIN 
                                        TBL_MS_REGION_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID LEFT JOIN
                                        TBL_MS_HO_REGION C ON B.REGIONAL_ID = C.REGIONAL_ID
                                    WHERE (C.HO_ID = @COST_CENTER) OR (B.REGIONAL_ID = @COST_CENTER) OR (A.CLUSTER_ID = @COST_CENTER) OR (A.BRANCH_ID = @COST_CENTER)
                                    GROUP BY B.CLUSTER_ID
                                ) C ON A.CLUSTER_ID = C.CLUSTER_ID
                                WHERE 
                                     (TARGET_YEAR = '' + @TARGET_YEAR + '')
                                     AND (RTRIM(B.UPDATE_BY) <> '' + @UPDATE_BY + '')
                                ");
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, UpdateBy);
            db.AddInParameter(cmd, "@COST_CENTER", DbType.String, CostCenter);            
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    list.Add(new ClusterTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11)));
                }
                dr.Close();
            }
            return list;
        }

        public ClusterTargetInfo Get(string id, string TargetYear)
        {
            ClusterTargetInfo bi = new ClusterTargetInfo();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT A.CLUSTER_ID, B.CLUSTER_NAME, A.TARGET_YEAR, A.TARGET_AMOUNT, A.NOA, A.TARGET_AMOUNT_REAL, A.NOA_REAL, A.ENTRY_BY, A.ENTRY_DATE, A.UPDATE_BY, A.UPDATE_DATE 
                                FROM TBL_CLUSTER_TARGET_PLAN A INNER JOIN TBL_MS_CLUSTER B ON A.CLUSTER_ID = B.CLUSTER_ID WHERE (A.CLUSTER_ID = '' + @CLUSTER_ID + '') AND (A.TARGET_YEAR = '' + @TARGET_YEAR + '') 
                                ");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String,TargetYear);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new ClusterTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10));
                }
                dr.Close();
            }

            return bi;
        }

        public ClusterTargetInfo GetClusterTarget(string id, string TargetYear)
        {
            ClusterTargetInfo bi = new ClusterTargetInfo();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT ISNULL(SUM(A.TARGET_AMOUNT),0) AS TARGET_AMOUNT, ISNULL(SUM(A.NOA),0) AS NOA, ISNULL(SUM(A.TARGET_AMOUNT_REAL),0) AS TARGET_AMOUNT_REAL, ISNULL(SUM(A.NOA_REAL),0) AS NOA_REAL FROM TBL_BRANCH_TARGET_PLAN A
                                INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID 
                                WHERE 
	                                B.CLUSTER_ID = '' + @CLUSTER_ID + ''
	                                AND A.TARGET_YEAR = '' + @TARGET_YEAR + ''
                                ");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String,TargetYear);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new ClusterTargetInfo(dr.GetInt64(0), dr.GetInt32(1), dr.GetInt64(2), dr.GetInt32(3));
                }
                dr.Close();
            }
            return bi;
        }

        public ClusterTargetInfo GetClusterTargetTotal(string cluster_id, string branch_id, string TargetYear)
        {
            ClusterTargetInfo bi = new ClusterTargetInfo();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT ISNULL(SUM(A.TARGET_AMOUNT),0) AS TARGET_AMOUNT, ISNULL(SUM(A.NOA),0) AS NOA, ISNULL(SUM(A.TARGET_AMOUNT_REAL),0) AS TARGET_AMOUNT_REAL, ISNULL(SUM(A.NOA_REAL),0) AS NOA_REAL FROM TBL_BRANCH_TARGET_PLAN A
                                INNER JOIN TBL_MS_CLUSTER_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID 
                                WHERE 
	                                B.CLUSTER_ID = '' + @CLUSTER_ID + ''
	                                AND A.TARGET_YEAR = '' + @TARGET_YEAR + ''
                                    AND A.BRANCH_ID <> @BRANCH_ID
                                ");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, cluster_id);
            db.AddInParameter(cmd, "@BRANCH_ID", DbType.String, branch_id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new ClusterTargetInfo(dr.GetInt64(0), dr.GetInt32(1), dr.GetInt64(2), dr.GetInt32(3));
                }
                dr.Close();
            }
            return bi;
        }

        public ClusterTargetInfo GetApprovalByID(string id, string TargetYear)
        {
            ClusterTargetInfo bi = new ClusterTargetInfo();
            //Database db = DatabaseFactory.CreateDatabase();
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"
                                SELECT 
            		                    A.CLUSTER_ID, 
            		                    A.CLUSTER_NAME,
            		                    B.TARGET_YEAR,
                                        B.TARGET_AMOUNT,
            		                    B.NOA,
                                        B.TARGET_AMOUNT_REAL,
            		                    B.NOA_REAL,
            		                    B.ENTRY_BY,
            		                    B.ENTRY_DATE,
            		                    B.UPDATE_BY,
            		                    B.UPDATE_DATE,
                                        B.ACT_TYPE
                                FROM TBL_MS_CLUSTER A INNER JOIN TBL_CLUSTER_TARGET_PLAN_TMP B
                                ON A.CLUSTER_ID = B.CLUSTER_ID
                                WHERE (B.CLUSTER_ID = '' + @CLUSTER_ID + '') AND (B.TARGET_YEAR = '' + @TARGET_YEAR + '')
                                ");
            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, id);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    bi = new ClusterTargetInfo(dr.GetString(0), dr.GetString(1), dr.GetString(2), dr.GetInt64(3), dr.GetInt32(4), dr.GetInt64(5), dr.GetInt32(6), dr.GetString(7), dr.GetDateTime(8), dr.GetString(9), dr.GetDateTime(10), dr.GetString(11));
                }
                dr.Close();
            }

            return bi;
        }

        public int Save(ClusterTargetInfo ClusterTarget)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                DbCommand cmd = db.GetSqlStringCommand(@"
            	            UPDATE TBL_CLUSTER_TARGET_PLAN_TMP SET
            		            TARGET_AMOUNT = @TARGET_AMOUNT, 
            		            NOA = @NOA, 
            		            TARGET_AMOUNT_REAL = @TARGET_AMOUNT_REAL, 
            		            NOA_REAL = @NOA_REAL, 
                                UPDATE_BY = @UPDATE_BY, 
                                UPDATE_DATE = @UPDATE_DATE, 
                                ACT_TYPE = @ACT_TYPE 
            	            WHERE (CLUSTER_ID = '' + @CLUSTER_ID + '') AND (TARGET_YEAR = '' + @TARGET_YEAR + '');
            	            IF @@ROWCOUNT = 0
                            INSERT INTO TBL_CLUSTER_TARGET_PLAN_TMP(CLUSTER_ID, TARGET_YEAR, TARGET_AMOUNT, NOA, TARGET_AMOUNT_REAL, NOA_REAL, ENTRY_BY, ENTRY_DATE, UPDATE_BY, UPDATE_DATE, ACT_TYPE) 
                            VALUES(@CLUSTER_ID, @TARGET_YEAR, @TARGET_AMOUNT, @NOA, @TARGET_AMOUNT_REAL, @NOA_REAL, @ENTRY_BY, @ENTRY_DATE, @UPDATE_BY, @UPDATE_DATE, @ACT_TYPE)
                            ");
                db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterTarget.ClusterID);
                db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, ClusterTarget.TargetYear);
                db.AddInParameter(cmd, "@TARGET_AMOUNT", DbType.Int64, ClusterTarget.TargetAmount);
                db.AddInParameter(cmd, "@NOA", DbType.Int32, ClusterTarget.Noa);
                db.AddInParameter(cmd, "@TARGET_AMOUNT_REAL", DbType.Int64, ClusterTarget.TargetAmountReal);
                db.AddInParameter(cmd, "@NOA_REAL", DbType.Int32, ClusterTarget.NoaReal);
                db.AddInParameter(cmd, "@ENTRY_BY", DbType.String, ClusterTarget.EntryBy);
                db.AddInParameter(cmd, "@ENTRY_DATE", DbType.DateTime, ClusterTarget.EntryDate);
                db.AddInParameter(cmd, "@UPDATE_BY", DbType.String, ClusterTarget.UpdateBy);
                db.AddInParameter(cmd, "@UPDATE_DATE", DbType.DateTime, ClusterTarget.UpdateDate);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ClusterTarget.ActType);

                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public int Approve(string ClusterID, string TargetYear, string ActType)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();
                DbCommand cmd = db.GetStoredProcCommand("CLUSTER_TARGET_PLAN_APPROVAL");

                db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterID);
                db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);
                db.AddInParameter(cmd, "@ACT_TYPE", DbType.String, ActType);

                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public int Reject(string ClusterID, string TargetYear)
        {
            int result = 0;
            try
            {
                Database db = DBConnection.CreateDatabase();

                string sql = String.Format("DELETE FROM TBL_CLUSTER_TARGET_PLAN_TMP WHERE (CLUSTER_ID = '{0}') AND (TARGET_YEAR = '{1}');", ClusterID, TargetYear);
                DbCommand cmd = db.GetSqlStringCommand(sql);

                result = db.ExecuteNonQuery(cmd);
            }
            catch (Exception) { }
            return result;
        }

        public bool IsDependency(string ClusterID, string TargetYear)
        {
            object result;
            Database db = DBConnection.CreateDatabase();
            DbCommand cmd = db.GetSqlStringCommand(@"SELECT ISNULL(SUM(TARGET_AMOUNT),0)
                FROM TBL_BRANCH_TARGET_PLAN 
                INNER JOIN TBL_MS_CLUSTER_BRANCH ON TBL_BRANCH_TARGET_PLAN.BRANCH_ID = TBL_MS_CLUSTER_BRANCH.BRANCH_ID
                WHERE TBL_MS_CLUSTER_BRANCH.CLUSTER_ID = @CLUSTER_ID AND TBL_BRANCH_TARGET_PLAN.TARGET_YEAR = @TARGET_YEAR
            ");

            db.AddInParameter(cmd, "@CLUSTER_ID", DbType.String, ClusterID);
            db.AddInParameter(cmd, "@TARGET_YEAR", DbType.String, TargetYear);

            result = db.ExecuteScalar(cmd);
            if (result == null) return false;
            else if (Convert.ToDouble(result) > 0) return true;
            return false;
        }
	}
}